const mysql = require('mysql2/promise');

// 数据库连接配置 - 使用您指定的参数
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,  // MySQL 标准端口
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '123456',  // 您的数据库密码
  database: process.env.DB_NAME || 'myapp_db',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  timezone: '+08:00'  // 中国时区
};

// 创建连接池
const pool = mysql.createPool(dbConfig);

// 测试数据库连接
async function testConnection() {
  try {
    // 首先测试 MySQL 服务器连接（不指定数据库）
    const tempConfig = { ...dbConfig };
    delete tempConfig.database;
    const tempPool = mysql.createPool(tempConfig);
    
    const tempConnection = await tempPool.getConnection();
    console.log('✅ MySQL 服务器连接成功');
    console.log(`📍 服务器信息: ${dbConfig.user}@${dbConfig.host}:${dbConfig.port}`);
    tempConnection.release();
    await tempPool.end();
    
    // 然后测试目标数据库连接
    try {
      const connection = await pool.getConnection();
      console.log(`✅ 数据库 '${dbConfig.database}' 连接成功`);
      connection.release();
    } catch (dbError) {
      if (dbError.code === 'ER_BAD_DB_ERROR') {
        console.log(`⚠️  数据库 '${dbConfig.database}' 不存在，稍后将自动创建`);
      } else {
        throw dbError;
      }
    }
    
    return true;
  } catch (error) {
    console.error('❌ 数据库连接失败:', error.message);
    console.log(`🔍 检查信息: ${dbConfig.user}@${dbConfig.host}:${dbConfig.port}`);
    return false;
  }
}

// 初始化数据库和表
async function initializeDatabase() {
  try {
    // 首先创建数据库（如果不存在）
    const createDatabaseQuery = `CREATE DATABASE IF NOT EXISTS \`${dbConfig.database}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`;
    
    // 创建临时连接（不指定数据库）来创建数据库
    const tempConfig = { ...dbConfig };
    delete tempConfig.database;
    const tempPool = mysql.createPool(tempConfig);
    
    await tempPool.execute(createDatabaseQuery);
    console.log(`✅ 数据库 '${dbConfig.database}' 创建/确认成功`);
    await tempPool.end();
    
    // 创建用户表
    const createUsersTable = `
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
        name VARCHAR(100) NOT NULL COMMENT '用户姓名',
        email VARCHAR(100) UNIQUE NOT NULL COMMENT '邮箱地址',
        phone VARCHAR(20) COMMENT '电话号码',
        age INT COMMENT '年龄',
        status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_email (email),
        INDEX idx_status (status),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户信息表'
    `;
    
    await pool.execute(createUsersTable);
    console.log('✅ 用户表初始化成功');

    // 创建食物模板表
    const createFoodTemplatesTable = `
      CREATE TABLE IF NOT EXISTS food_templates (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '模板ID',
        name VARCHAR(100) NOT NULL COMMENT '食物名称',
        icon VARCHAR(500) COMMENT '图标地址',
        can_room_temp TINYINT DEFAULT 0 COMMENT '是否可选常温(0:否 1:是)',
        can_refrigerated TINYINT DEFAULT 0 COMMENT '是否可选冷藏(0:否 1:是)',
        can_frozen TINYINT DEFAULT 0 COMMENT '是否可选冷冻(0:否 1:是)',
        food_type VARCHAR(50) NOT NULL COMMENT '食物种类',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_food_type (food_type),
        INDEX idx_name (name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='食物模板表'
    `;

    await pool.execute(createFoodTemplatesTable);
    console.log('✅ 食物模板表初始化成功');

    // 创建冰箱表
    const createRefrigeratorsTable = `
      CREATE TABLE IF NOT EXISTS refrigerators (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '冰箱ID',
        name VARCHAR(100) NOT NULL COMMENT '冰箱名称',
        location VARCHAR(100) NOT NULL COMMENT '位置',
        brand VARCHAR(50) COMMENT '品牌',
        model VARCHAR(100) COMMENT '型号',
        capacity DECIMAL(8,2) COMMENT '容量(升)',
        temperature_control ENUM('manual', 'automatic') DEFAULT 'automatic' COMMENT '温度控制方式',
        status ENUM('active', 'inactive', 'maintenance') DEFAULT 'active' COMMENT '状态',
        purchase_date DATE COMMENT '购买日期',
        warranty_until DATE COMMENT '保修截止日期',
        notes TEXT COMMENT '备注信息',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_location (location),
        INDEX idx_status (status),
        INDEX idx_brand (brand)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='冰箱表'
    `;

    await pool.execute(createRefrigeratorsTable);
    console.log('✅ 冰箱表初始化成功');

    // 创建食物表
    const createFoodsTable = `
      CREATE TABLE IF NOT EXISTS foods (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '食物ID',
        notes VARCHAR(200) COMMENT '备注',
        put_in_date DATE NOT NULL COMMENT '放入时间',
        shelf_life_days INT NOT NULL COMMENT '保质期(天数)',
        expiry_date DATE NOT NULL COMMENT '截止时间',
        reminder_date DATE COMMENT '提醒日期',
        temperature_status ENUM('room_temp', 'refrigerated', 'frozen') NOT NULL COMMENT '温度状态',
        template_id INT NOT NULL COMMENT '关联的模板ID',
        refrigerator_id INT NOT NULL COMMENT '关联的冰箱ID',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_template_id (template_id),
        INDEX idx_refrigerator_id (refrigerator_id),
        INDEX idx_expiry_date (expiry_date),
        INDEX idx_reminder_date (reminder_date),
        INDEX idx_temperature_status (temperature_status),
        FOREIGN KEY (template_id) REFERENCES food_templates(id) ON DELETE CASCADE,
        FOREIGN KEY (refrigerator_id) REFERENCES refrigerators(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='食物表'
    `;

    await pool.execute(createFoodsTable);
    console.log('✅ 食物表初始化成功');

    // 创建食品种类表
    const createFoodCategoriesTable = `
      CREATE TABLE IF NOT EXISTS food_categories (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '种类ID',
        type_name VARCHAR(50) NOT NULL COMMENT '种类名称',
        type_value VARCHAR(50) NOT NULL UNIQUE COMMENT '种类值',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_type_value (type_value),
        INDEX idx_type_name (type_name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='食品种类表'
    `;

    await pool.execute(createFoodCategoriesTable);
    console.log('✅ 食品种类表初始化成功');
    
    // 检查是否有数据，如果没有则插入示例数据
    const [rows] = await pool.execute('SELECT COUNT(*) as count FROM users');
    if (rows[0].count === 0) {
      console.log('🔄 正在插入示例用户数据...');
      
      const sampleUsers = [
        ['张三', 'zhangsan@example.com', '13800138000', 25],
        ['李四', 'lisi@example.com', '13900139000', 30],
        ['王五', 'wangwu@example.com', '13700137000', 28]
      ];
      
      for (const user of sampleUsers) {
        await pool.execute(
          'INSERT INTO users (name, email, phone, age) VALUES (?, ?, ?, ?)',
          user
        );
      }
      console.log('✅ 示例用户数据插入成功');
    }

    // 插入示例冰箱数据
    const [refrigeratorRows] = await pool.execute('SELECT COUNT(*) as count FROM refrigerators');
    if (refrigeratorRows[0].count === 0) {
      console.log('🔄 正在插入示例冰箱数据...');
      
      const sampleRefrigerators = [
        ['主厨房冰箱', '厨房', '海尔', 'BCD-258WDVMU1', 258.00, 'automatic', 'active', '2023-01-15', '2026-01-15', '主要存储冰箱，日常使用'],
        ['客厅小冰箱', '客厅', '美的', 'BC-93CM', 93.00, 'manual', 'active', '2023-06-20', '2026-06-20', '客厅小型冰箱，存放饮料和水果'],
        ['地下室冰柜', '地下室', '容声', 'BD-230WX', 230.00, 'manual', 'active', '2022-12-10', '2025-12-10', '冷冻专用冰柜']
      ];
      
      for (const refrigerator of sampleRefrigerators) {
        await pool.execute(
          'INSERT INTO refrigerators (name, location, brand, model, capacity, temperature_control, status, purchase_date, warranty_until, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
          refrigerator
        );
      }
      console.log('✅ 示例冰箱数据插入成功');
    }

    // 插入示例食物模板数据
    const [templateRows] = await pool.execute('SELECT COUNT(*) as count FROM food_templates');
    if (templateRows[0].count === 0) {
      console.log('🔄 正在插入示例食物模板数据...');
      
      const sampleFoodTemplates = [
        // 蔬菜类
        ['白菜', '/icons/vegetables/cabbage.png', 1, 1, 1, '蔬菜'],
        ['胡萝卜', '/icons/vegetables/carrot.png', 1, 1, 1, '蔬菜'],
        ['土豆', '/icons/vegetables/potato.png', 1, 1, 1, '蔬菜'],
        ['西红柿', '/icons/vegetables/tomato.png', 1, 1, 0, '蔬菜'],
        ['黄瓜', '/icons/vegetables/cucumber.png', 0, 1, 0, '蔬菜'],
        ['菠菜', '/icons/vegetables/spinach.png', 0, 1, 1, '蔬菜'],
        
        // 水果类
        ['苹果', '/icons/fruits/apple.png', 1, 1, 1, '水果'],
        ['香蕉', '/icons/fruits/banana.png', 1, 1, 1, '水果'],
        ['橙子', '/icons/fruits/orange.png', 1, 1, 0, '水果'],
        ['葡萄', '/icons/fruits/grape.png', 0, 1, 1, '水果'],
        ['草莓', '/icons/fruits/strawberry.png', 0, 1, 1, '水果'],
        
        // 肉类
        ['猪肉', '/icons/meat/pork.png', 0, 1, 1, '肉类'],
        ['牛肉', '/icons/meat/beef.png', 0, 1, 1, '肉类'],
        ['鸡肉', '/icons/meat/chicken.png', 0, 1, 1, '肉类'],
        ['鱼肉', '/icons/meat/fish.png', 0, 1, 1, '肉类'],
        
        // 奶制品
        ['牛奶', '/icons/dairy/milk.png', 0, 1, 1, '奶制品'],
        ['酸奶', '/icons/dairy/yogurt.png', 0, 1, 1, '奶制品'],
        ['奶酪', '/icons/dairy/cheese.png', 0, 1, 1, '奶制品'],
        
        // 主食
        ['米饭', '/icons/staple/rice.png', 1, 1, 1, '主食'],
        ['面条', '/icons/staple/noodles.png', 1, 1, 1, '主食'],
        ['面包', '/icons/staple/bread.png', 1, 1, 1, '主食'],
        
        // 饮料
        ['可乐', '/icons/drinks/cola.png', 1, 1, 0, '饮料'],
        ['果汁', '/icons/drinks/juice.png', 1, 1, 1, '饮料'],
        ['茶', '/icons/drinks/tea.png', 1, 1, 0, '饮料']
      ];
      
      for (const template of sampleFoodTemplates) {
        await pool.execute(
          'INSERT INTO food_templates (name, icon, can_room_temp, can_refrigerated, can_frozen, food_type) VALUES (?, ?, ?, ?, ?, ?)',
          template
        );
      }
      console.log('✅ 示例食物模板数据插入成功');
    }

    // 插入示例食物数据 (修复template_id引用问题)
    const [foodRows] = await pool.execute('SELECT COUNT(*) as count FROM foods');
    if (foodRows[0].count === 0) {
      console.log('🔄 正在插入示例食物数据...');
      
      const sampleFoods = [
        // 主厨房冰箱的食物 (refrigerator_id = 1) - 使用有效的template_id (1-24)
        ['新鲜白菜，菜市场购买', '2024-01-15', 7, '2024-01-22', '2024-01-20', 'refrigerated', 1, 1],
        ['有机胡萝卜', '2024-01-14', 14, '2024-01-28', '2024-01-25', 'refrigerated', 2, 1],
        ['土豆，适合做土豆丝', '2024-01-10', 21, '2024-01-31', '2024-01-28', 'room_temp', 3, 1],
        ['新鲜西红柿', '2024-01-16', 5, '2024-01-21', '2024-01-19', 'refrigerated', 4, 1],
        ['脆嫩黄瓜', '2024-01-17', 3, '2024-01-20', '2024-01-19', 'refrigerated', 5, 1],
        ['新鲜苹果，红富士', '2024-01-12', 10, '2024-01-22', '2024-01-20', 'refrigerated', 7, 1],
        ['进口香蕉', '2024-01-18', 4, '2024-01-22', '2024-01-21', 'room_temp', 8, 1],
        ['新鲜猪肉', '2024-01-17', 2, '2024-01-19', '2024-01-18', 'refrigerated', 12, 1],
        ['鸡胸肉', '2024-01-16', 3, '2024-01-19', '2024-01-18', 'refrigerated', 14, 1],
        ['新鲜牛奶', '2024-01-15', 5, '2024-01-20', '2024-01-19', 'refrigerated', 16, 1],
        ['酸奶杯装', '2024-01-14', 7, '2024-01-21', '2024-01-19', 'refrigerated', 17, 1],
        
        // 客厅小冰箱的食物 (refrigerator_id = 2) - 修正template_id
        ['可乐，聚会用', '2024-01-10', 180, '2024-07-08', '2024-06-01', 'refrigerated', 22, 2],
        ['橙汁，100%纯果汁', '2024-01-15', 7, '2024-01-22', '2024-01-20', 'refrigerated', 23, 2],
        ['草莓，盒装', '2024-01-17', 2, '2024-01-19', '2024-01-18', 'refrigerated', 11, 2],
        ['新鲜葡萄', '2024-01-16', 5, '2024-01-21', '2024-01-19', 'refrigerated', 10, 2],
        ['酸奶，希腊风味', '2024-01-14', 10, '2024-01-24', '2024-01-22', 'refrigerated', 17, 2],
        
        // 地下室冰柜的食物 (refrigerator_id = 3)
        ['冷冻牛肉', '2024-01-01', 90, '2024-04-01', '2024-03-15', 'frozen', 13, 3],
        ['冷冻鱼肉', '2024-01-05', 60, '2024-03-05', '2024-02-20', 'frozen', 15, 3],
        ['速冻饺子', '2024-01-10', 180, '2024-07-08', '2024-06-01', 'frozen', 20, 3],
        ['冷冻面包', '2024-01-12', 30, '2024-02-11', '2024-02-05', 'frozen', 21, 3],
        ['冷冻菠菜', '2024-01-08', 180, '2024-07-06', '2024-06-01', 'frozen', 6, 3],
        ['冷冻果汁', '2024-01-05', 365, '2025-01-05', '2024-12-01', 'frozen', 23, 3]
      ];
      
      for (const food of sampleFoods) {
        try {
          await pool.execute(
            'INSERT INTO foods (notes, put_in_date, shelf_life_days, expiry_date, reminder_date, temperature_status, template_id, refrigerator_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            food
          );
        } catch (error) {
          console.log(`⚠️  跳过插入食物数据: ${error.message}`);
        }
      }
      console.log('✅ 示例食物数据插入完成');
    }

    // 插入食品种类数据
    const [categoryRows] = await pool.execute('SELECT COUNT(*) as count FROM food_categories');
    if (categoryRows[0].count === 0) {
      console.log('🔄 正在插入食品种类数据...');
      
      const sampleFoodCategories = [
        ['全部', 'all'],
        ['蔬菜', 'vegetable'],
        ['水果', 'fruit'],
        ['蛋奶', 'egg'],
        ['肉类', 'meat'],
        ['水产', 'fish'],
        ['调味', 'spice'],
        ['主食', 'main'],
        ['茶叶', 'tea'],
        ['饮品', 'drink'],
        ['零食', 'snack'],
        ['烘焙', 'bake'],
        ['酒水', 'alcohol'],
        ['速食', 'fast'],
        ['其他', 'other'],
        ['豆制品', 'bean']
      ];
      
      for (const category of sampleFoodCategories) {
        await pool.execute(
          'INSERT INTO food_categories (type_name, type_value) VALUES (?, ?)',
          category
        );
      }
      console.log('✅ 食品种类数据插入成功');
    }

    // ==================== 商品相关表创建 ====================
    
    // 创建商品分类表
    const createGoodsCategoriesTable = `
      CREATE TABLE IF NOT EXISTS goods_categories (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID',
        sort INT DEFAULT 0 COMMENT '排序',
        icon VARCHAR(500) COMMENT '分类图标',
        name VARCHAR(100) NOT NULL COMMENT '分类名称',
        is_show_backstage TINYINT DEFAULT 0 COMMENT '是否在后台显示(0:否 1:是)',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_sort (sort),
        INDEX idx_name (name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品分类表'
    `;

    await pool.execute(createGoodsCategoriesTable);
    console.log('✅ 商品分类表初始化成功');

    // 创建商品主表
    const createGoodsTable = `
      CREATE TABLE IF NOT EXISTS goods (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
        category_id INT NOT NULL COMMENT '分类ID',
        name VARCHAR(200) NOT NULL COMMENT '商品名称',
        price DECIMAL(10,2) NOT NULL COMMENT '价格',
        stock DECIMAL(12,2) DEFAULT 0 COMMENT '库存',
        unit VARCHAR(20) DEFAULT '件' COMMENT '单位',
        content TEXT COMMENT '商品描述',
        images VARCHAR(500) COMMENT '主图地址',
        cover_img VARCHAR(500) COMMENT '封面图',
        pack_cost DECIMAL(10,2) DEFAULT 0 COMMENT '包装费',
        sales INT DEFAULT 0 COMMENT '销量',
        goods_type TINYINT DEFAULT 1 COMMENT '商品类型(1:实物 2:虚拟)',
        use_property TINYINT DEFAULT 0 COMMENT '是否使用规格(0:否 1:是)',
        type TINYINT DEFAULT 1 COMMENT '类型',
        is_label TINYINT DEFAULT 0 COMMENT '是否有标签(0:否 1:是)',
        sort INT DEFAULT 0 COMMENT '排序',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        FOREIGN KEY (category_id) REFERENCES goods_categories(id) ON DELETE CASCADE,
        INDEX idx_category_id (category_id),
        INDEX idx_name (name),
        INDEX idx_price (price),
        INDEX idx_sort (sort)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品主表'
    `;

    await pool.execute(createGoodsTable);
    console.log('✅ 商品主表初始化成功');

    // 创建商品规格表
    const createGoodsPropertiesTable = `
      CREATE TABLE IF NOT EXISTS goods_properties (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '规格ID',
        goods_id INT NULL COMMENT '商品ID（可为空，商品删除后保留规格）',
        name VARCHAR(100) NOT NULL COMMENT '规格名称',
        \`desc\` TEXT COMMENT '规格描述',
        is_open_checkbox TINYINT DEFAULT 0 COMMENT '是否开启复选框(0:否 1:是)',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE SET NULL,
        INDEX idx_goods_id (goods_id),
        INDEX idx_name (name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品规格表'
    `;

    await pool.execute(createGoodsPropertiesTable);
    console.log('✅ 商品规格表初始化成功');

    // 创建商品规格值表
    const createGoodsPropertyValuesTable = `
      CREATE TABLE IF NOT EXISTS goods_property_values (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '规格值ID',
        property_id INT NOT NULL COMMENT '规格ID',
        value VARCHAR(100) NOT NULL COMMENT '规格值',
        code VARCHAR(100) COMMENT '规格值代码',
        is_default TINYINT DEFAULT 0 COMMENT '是否默认(0:否 1:是)',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        FOREIGN KEY (property_id) REFERENCES goods_properties(id) ON DELETE CASCADE,
        INDEX idx_property_id (property_id),
        INDEX idx_code (code)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品规格值表'
    `;

    await pool.execute(createGoodsPropertyValuesTable);
    console.log('✅ 商品规格值表初始化成功');

    // 创建商品实体表(SKU)
    const createGoodsEntitiesTable = `
      CREATE TABLE IF NOT EXISTS goods_entities (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '实体ID',
        entity_id VARCHAR(16) UNIQUE NOT NULL COMMENT '16位实体ID',
        goods_id INT NOT NULL COMMENT '商品ID',
        spec_id VARCHAR(100) COMMENT '规格ID',
        trade_mark VARCHAR(100) COMMENT '商标',
        stock DECIMAL(12,2) DEFAULT 0 COMMENT '库存',
        image VARCHAR(500) COMMENT '实体图片',
        num INT DEFAULT 1 COMMENT '数量',
        price DECIMAL(10,2) NOT NULL COMMENT '价格',
        membership_price DECIMAL(10,2) DEFAULT 0 COMMENT '会员价',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE,
        INDEX idx_entity_id (entity_id),
        INDEX idx_goods_id (goods_id),
        INDEX idx_trade_mark (trade_mark)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品实体表'
    `;

    await pool.execute(createGoodsEntitiesTable);
    console.log('✅ 商品实体表初始化成功');

    // 创建商品图片表
    const createGoodsImagesTable = `
      CREATE TABLE IF NOT EXISTS goods_images (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '图片ID',
        goods_id INT NOT NULL COMMENT '商品ID',
        image_url VARCHAR(500) NOT NULL COMMENT '图片地址',
        sort INT DEFAULT 0 COMMENT '排序',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        FOREIGN KEY (goods_id) REFERENCES goods(id) ON DELETE CASCADE,
        INDEX idx_goods_id (goods_id),
        INDEX idx_sort (sort)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品图片表'
    `;

    await pool.execute(createGoodsImagesTable);
    console.log('✅ 商品图片表初始化成功');

    // ==================== 插入商品测试数据 ====================
    
    // 插入商品分类数据
    const [goodsCategoryRows] = await pool.execute('SELECT COUNT(*) as count FROM goods_categories');
    if (goodsCategoryRows[0].count === 0) {
      console.log('🔄 正在插入商品分类数据...');
      
      const sampleGoodsCategories = [
        [1, '', 6905, '外卖', 0],
        [2, 'https://img-shop.qmimg.cn/s23107/2019/04/30/458c5a14fb2f190f96.png?imageView2/0/w/200/h/200', 6208, '新品推荐', 0],
        [3, 'https://img-shop.qmimg.cn/s23107/2019/04/30/458c5a14fb2f190f96.png?imageView2/0/w/200/h/200', 6387, '招牌热卖', 0]
      ];
      
      for (const category of sampleGoodsCategories) {
        await pool.execute(
          'INSERT INTO goods_categories (sort, icon, id, name, is_show_backstage) VALUES (?, ?, ?, ?, ?)',
          category
        );
      }
      console.log('✅ 商品分类数据插入成功');
    }

    // 插入商品数据
    const [goodsRows] = await pool.execute('SELECT COUNT(*) as count FROM goods');
    if (goodsRows[0].count === 0) {
      console.log('🔄 正在插入商品数据...');
      
      const sampleGoods = [
        // 外卖分类
        [65825, 6905, '奈雪早餐', 18.50, 9999956.00, '件', '购买三明治,享早餐指定饮品半价', 'https://img-shop.qmimg.cn/s23107/2020/04/19/fda6dd99c83af02353.jpg?imageView2/2/w/400/h/400', '', 0.00, 487, 1, 0, 2, 0, 99],
        
        // 新品推荐分类
        [24516, 6208, '脏脏王', 18.00, 10485.00, '件', '酥软口感,进口奶油搭配特制巧克力内馅', 'https://img-shop.qmimg.cn/s23107/2020/04/29/4a62ee45dd527609ed.jpg?imageView2/2/w/400/h/400', '', 0.00, 1278, 1, 0, 1, 0, 3],
        [24517, 6208, '抹茶王', 18.00, 10308.00, '件', '精选北川半兵卫抹茶粉和秘制红豆泥', 'https://img-shop.qmimg.cn/s23107/2020/04/29/99daa7b20061efab10.jpg?imageView2/2/w/400/h/400', '', 0.00, 1228, 1, 0, 1, 0, 4],
        [68339, 6208, '霸气杨梅', 30.00, 9999817.00, '件', '饱满大颗的新鲜杨梅,满杯手工去核,搭配茉莉初雪茶底,清爽多汁', 'https://img-shop.qmimg.cn/s23107/2020/04/26/559a075d81060b23c7.jpg?imageView2/2/w/400/h/400', '', 0.00, 44429, 2, 1, 1, 0, 0],
        [68345, 6208, '霸气酸奶杨梅', 32.00, 999950.00, '件', '霸气杨梅和酸奶的首次搭配,甘甜可口', 'https://img-shop.qmimg.cn/s23107/2020/04/26/ea8fc439fddf2f62e3.jpg?imageView2/2/w/400/h/400', '', 0.00, 16322, 2, 1, 1, 0, 2],
        
        // 招牌热卖分类  
        [31826, 6387, '霸气榴莲王', 28.00, 1000498.00, '件', '马来西亚D24榴莲王果肉+芒果干，爆浆的榴莲馅，这款是榴莲控的最爱。', 'https://img-shop.qmimg.cn/s23107/2019/04/28/be484557ff7cfa4dba.jpg?imageView2/2/w/400/h/400', '', 0.00, 70462, 1, 0, 1, 0, 2],
        [31507, 6387, '霸气橙子', 25.00, 998101.00, '件', '奈雪明星产品。优选进口新奇士橙，搭配严选茉莉毛尖茶底，橙意满满。', 'https://img-shop.qmimg.cn/s23107/2019/04/26/2a4b2697bec6f7e502.jpg?imageView2/2/w/400/h/400', '', 0.00, 199583, 2, 1, 1, 0, 2],
        [31532, 6387, '霸气芝士葡萄', 28.00, 1000314.00, '件', '精选优质巨峰葡萄、手工去皮去籽、加入带兰桂花香，经中度焙火的金观音茶中，搭配轻盈香甜芝士奶盖', 'https://img-shop.qmimg.cn/s23107/2020/03/19/a3ee1fa72435259a73.jpg?imageView2/2/w/400/h/400', '', 0.00, 5104, 2, 1, 1, 0, 4],
        [16808, 6387, '草莓魔法棒', 20.00, 10704.00, '件', '大大的一根魔法棒，加入淡奶油和进口酵母，配上经典的草莓鲜果。好看的外表和经典的口味造就的一款奈雪明星产品。', 'https://img-shop.qmimg.cn/s23107/2019/04/28/94d8440ab7b4fed802.jpg?imageView2/2/w/400/h/400', '', 0.00, 115495, 2, 0, 1, 0, 5],
        [31424, 6387, '霸气芝士草莓', 29.00, 1002053.00, '件', '奈雪明星产品。选用奈雪自有草莓园新鲜草莓，搭配严选茉莉毛尖茶底，淋上轻盈香滑的芝士奶盖。', 'https://img-shop.qmimg.cn/s23107/2019/04/26/1cb88e6cd2fbcefb2a.jpg?imageView2/2/w/400/h/400', '', 0.00, 125010, 2, 1, 1, 0, 99]
      ];
      
      for (const goods of sampleGoods) {
        await pool.execute(
          'INSERT INTO goods (id, category_id, name, price, stock, unit, content, images, cover_img, pack_cost, sales, goods_type, use_property, type, is_label, sort) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
          goods
        );
      }
      console.log('✅ 商品数据插入成功');
    }

    // 插入商品规格数据（仅对use_property=1的商品）
    const [propertiesRows] = await pool.execute('SELECT COUNT(*) as count FROM goods_properties');
    if (propertiesRows[0].count === 0) {
      console.log('🔄 正在插入商品规格数据...');
      
      const sampleProperties = [
        // 霸气杨梅的温度规格
        [68339, '温度', null, 0],
        // 霸气酸奶杨梅的温度规格  
        [68345, '温度', null, 0],
        // 霸气橙子的温度规格
        [31507, '温度', null, 0],
        // 霸气芝士葡萄的配料规格
        [31532, '配料', null, 0],
        // 霸气芝士葡萄的温度规格
        [31532, '温度', null, 0],
        // 霸气芝士草莓的配料规格
        [31424, '配料', null, 0],
        // 霸气芝士草莓的温度规格
        [31424, '温度', null, 0]
      ];
      
      for (const property of sampleProperties) {
        await pool.execute(
          'INSERT INTO goods_properties (goods_id, name, `desc`, is_open_checkbox) VALUES (?, ?, ?, ?)',
          property
        );
      }
      console.log('✅ 商品规格数据插入成功');
    }

    // 插入商品规格值数据（基于动态生成的property_id）
    const [propertyValuesRows] = await pool.execute('SELECT COUNT(*) as count FROM goods_property_values');
    if (propertyValuesRows[0].count === 0) {
      console.log('🔄 正在插入商品规格值数据...');
      
      // 获取已创建的规格ID
      const [properties] = await pool.execute(`
        SELECT id, goods_id, name FROM goods_properties 
        ORDER BY id ASC
      `);
      
      for (const prop of properties) {
        if (prop.name === '温度') {
          // 温度规格值
          const tempValues = [
            ['标准冰', 'Z5EA54C3D0E4279185', 1],
            ['去冰', 'Z5EA54C3D0E9682750', 0]
          ];
          
          // 为橙子添加更多温度选项
          if (prop.goods_id === 31507) {
            tempValues.push(['热', 'Z5E5E2B48405642832', 0]);
            tempValues.push(['温', 'Z5E5E2B48409616050', 0]);
          }
          
          // 为芝士草莓调整默认值
          if (prop.goods_id === 31424) {
            tempValues[0][2] = 0; // 标准冰不是默认
            tempValues.push(['冰沙', 'Z5E5E2BC316A215600', 1]); // 冰沙为默认
            tempValues.push(['热', 'Z5E5E2BC317A175989', 0]);
            tempValues.push(['温', 'Z5E5E2BC317F896724', 0]);
          }
          
          for (const [value, code, isDefault] of tempValues) {
            await pool.execute(
              'INSERT INTO goods_property_values (property_id, value, code, is_default) VALUES (?, ?, ?, ?)',
              [prop.id, value, code, isDefault]
            );
          }
        } else if (prop.name === '配料') {
          // 配料规格值
          const ingredientValues = [
            ['标准(芝士)', 'Z5D612C34C8EF06658', 1],
            ['芝士换酸奶', 'Z5D612C34C93705460', 0]
          ];
          
          for (const [value, code, isDefault] of ingredientValues) {
            await pool.execute(
              'INSERT INTO goods_property_values (property_id, value, code, is_default) VALUES (?, ?, ?, ?)',
              [prop.id, value, code, isDefault]
            );
          }
        }
      }
      
      console.log('✅ 商品规格值数据插入成功');
    }

    // 插入商品实体数据
    const [entitiesRows] = await pool.execute('SELECT COUNT(*) as count FROM goods_entities');
    if (entitiesRows[0].count === 0) {
      console.log('🔄 正在插入商品实体数据...');
      
      const sampleEntities = [
        // 外卖分类
        ['9ad36aa96636c246', 65825, '', '11110090', 9999956.00, 's23107/2020/04/19/fda6dd99c83af02353.jpg', 1, 18.5, 0],
        
        // 新品推荐分类
        ['5d79de67251ea00e', 24516, '', '1212000070', 10485.00, 's23107/2020/04/29/4a62ee45dd527609ed.jpg', 1, 18.0, 0],
        ['2b3f1ea3ecabd22e', 24517, '', '1212000071', 10308.00, 's23107/2020/04/29/99daa7b20061efab10.jpg', 1, 18.0, 0],
        ['72cf1279c0a422ce', 68339, '', '1010010025', 9999817.00, 's23107/2020/04/26/559a075d81060b23c7.jpg', 1, 30.0, 0],
        ['c7c6f8fd34040338', 68345, '', '1010010043', 999950.00, 's23107/2020/04/26/ea8fc439fddf2f62e3.jpg', 1, 32.0, 0],
        
        // 招牌热卖分类
        ['2e3f40b8f6decb2a', 31826, '', '1212000017', 1000498.00, 's23107/2019/04/28/be484557ff7cfa4dba.jpg', 1, 28.0, 0],
        ['097aad038aeeb0ea', 31507, '', '1010010000', 998101.00, 's23107/2019/04/26/2a4b2697bec6f7e502.jpg', 1, 25.0, 0],
        ['f8114313a48c5c4a', 31532, '', '1010050008', 1000314.00, 's23107/2020/03/19/a3ee1fa72435259a73.jpg', 1, 28.0, 0],
        ['2e89b669b329c292', 16808, '', '1212000024', 10704.00, 's23107/2019/04/28/94d8440ab7b4fed802.jpg', 1, 20.0, 0],
        ['ee375ed5ae7f77eb', 31424, '', '1010050003', 1002053.00, 's23107/2019/04/26/1cb88e6cd2fbcefb2a.jpg', 1, 29.0, 0]
      ];
      
      for (const entity of sampleEntities) {
        await pool.execute(
          'INSERT INTO goods_entities (entity_id, goods_id, spec_id, trade_mark, stock, image, num, price, membership_price) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
          entity
        );
      }
      console.log('✅ 商品实体数据插入成功');
    }

    // 插入商品图片数据
    const [imagesRows] = await pool.execute('SELECT COUNT(*) as count FROM goods_images');
    if (imagesRows[0].count === 0) {
      console.log('🔄 正在插入商品图片数据...');
      
      const sampleImages = [
        // 每个商品的图片数组
        [65825, 'https://img-shop.qmimg.cn/s23107/2020/04/19/fda6dd99c83af02353.jpg?imageView2/2/w/600/h/600', 0],
        [24516, 'https://img-shop.qmimg.cn/s23107/2020/04/29/4a62ee45dd527609ed.jpg?imageView2/2/w/600/h/600', 0],
        [24517, 'https://img-shop.qmimg.cn/s23107/2020/04/29/99daa7b20061efab10.jpg?imageView2/2/w/600/h/600', 0],
        [68339, 'https://img-shop.qmimg.cn/s23107/2020/04/26/559a075d81060b23c7.jpg?imageView2/2/w/600/h/600', 0],
        [68345, 'https://img-shop.qmimg.cn/s23107/2020/04/26/ea8fc439fddf2f62e3.jpg?imageView2/2/w/600/h/600', 0],
        [31826, 'https://img-shop.qmimg.cn/s23107/2019/04/28/be484557ff7cfa4dba.jpg?imageView2/2/w/600/h/600', 0],
        [31507, 'https://img-shop.qmimg.cn/s23107/2019/04/26/2a4b2697bec6f7e502.jpg?imageView2/2/w/600/h/600', 0],
        [31532, 'https://img-shop.qmimg.cn/s23107/2020/03/19/a3ee1fa72435259a73.jpg?imageView2/2/w/600/h/600', 0],
        [16808, 'https://img-shop.qmimg.cn/s23107/2019/04/28/94d8440ab7b4fed802.jpg?imageView2/2/w/600/h/600', 0],
        [31424, 'https://img-shop.qmimg.cn/s23107/2019/04/26/1cb88e6cd2fbcefb2a.jpg?imageView2/2/w/600/h/600', 0]
      ];
      
      for (const image of sampleImages) {
        await pool.execute(
          'INSERT INTO goods_images (goods_id, image_url, sort) VALUES (?, ?, ?)',
          image
        );
      }
      console.log('✅ 商品图片数据插入成功');
    }
    
    return true;
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error.message);
    return false;
  }
}

module.exports = {
  pool,
  testConnection,
  initializeDatabase
};
